﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for Query
/// </summary>
public class Query
{
    public Query()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    //All connections
    //public static string ConnectionList = "SELECT * FROM Connection ORDER BY ConnectionName";

    public static string ConnectionList(object userId)
    {
        string connectionList = string.Format("SELECT * FROM Connection WHERE UserId = {0} ORDER BY ConnectionName", userId);
        return connectionList;
    }

    public static string Queries(int connectionId)
    {
        string queryList = string.Format("SELECT * FROM Query WHERE connectionId = {0} ORDER BY QueryName", connectionId);
        return queryList;
    }


    //All tables from database
    public static string TableList
    {
        get
        {
            return ObjectTypes(DatabaseObjectTypes.Tables);
        }
    }


    public static string ObjectTypes(DatabaseObjectTypes dot)
    {
        string selectStr = string.Format("SELECT * FROM INFORMATION_SCHEMA.Tables WHERE 1=1 ");

        if (dot == DatabaseObjectTypes.Tables)
        {
            selectStr += " AND TABLE_TYPE = 'BASE TABLE' ";
        }
        else if (dot == DatabaseObjectTypes.Views)
        {
            selectStr += " AND TABLE_TYPE = 'VIEW' ";
        }
        selectStr += "ORDER BY table_name";
        return selectStr;
    }

    public static string MappingList(int connectionId)
    {
        string selectStr = string.Format("SELECT * FROM ColumnMapping WHERE ConnectionId = {0}", connectionId);
        return selectStr;
    }
}

public enum DatabaseObjectTypes
{
    Tables,
    Views,
    All
}
